What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard
SQL has other functions such as:
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
SQL perform the following functions such as
SELECT, UPDATE, DELETE, INSERT, WHERE
SQL is a standard language for storing, manipulating and retrieving data in databases.
SQL could be used in the following database systems:
namely, MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.
To be able to run SQL, one has to structure the database properly.
For SQL to run a programm in the DB, one has to structure the Table.
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of:
CustomerID, CustomerName, ContactName, Address, City and PostalCode.
A field is a column in a table that is designed to maintain specific information about every record in the table.
This is an example of a customers table in a DB:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
Example
SELECT * FROM Computer kids Academy;
this sql statement says, select all (*) from Computer Kids Academy
there are a number of SQL commands that every user should get used to.
Example:
Some of The Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Column Example
The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:
Example
SELECT CustomerName, City FROM Customers;
your table will look like this:
CustomerName
City
Freddy
Berlin
Ana
Bomako
peter
México
john
London
Lhh
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values;
and sometimes you only want to list the different (distinct) values.
The SELECT DISTINCT statement is used to return only distinct (different) values.
(SELECT DISTINCT column2, column5, ...
FROM table_name; )
Example
SELECT City FROM Customers;
Berlin
Bomako
México
London
SELECT country FROM Customers;
SELECT Age FROM Customers;
The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note:
The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!
WHERE Clause Example
The following SQL statement selects all the customers from the country "Germany", in the"Customers" table:
Example
SELECT * FROM Customers
WHERE Country='Germany';
Operators in The WHERE Clause:
=
Equal
<>
Not equal. Note:
In some versions of SQL this operator may be written as !=
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
Between
Between an inclusive range
LIKE
Search for a pattern
IN
To specify multiple possible values for a column
SQL And, Or, Not
The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE
.
AND Example
This SQL statement selects all fields from "Customers" where country is "Brasil" AND city is "Rio":
Example
SELECT * FROM Customers
WHERE Country='Brasil' AND City='Rio';
OR Example
This SQL statement selects all fields from "Customers" where city is "Brasil" OR "Rio":
Example
SELECT * FROM Customers
WHERE City='Brasil' OR City='Rio';
NOT Example
The following SQL statement selects all fields from "Customers" where country is NOT "Brasil":
Example
SELECT * FROM Customers
WHERE NOT Country='Brasil';
Combining AND, OR and NOT
One can also combine the AND, OR and NOT operators.
SELECT * FROM Customers
WHERE Country='Brasil' AND (City='Rio' OR City='Brasillia');
ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:
Example
SELECT * FROM Customers
ORDER BY Country;
Number of Records:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
1
Rancho grande
Sergio Gutiérrez
Av. del Libertador 900
Buenos Aires
1010
Argentina
2
Océano Atlántico Ltda.
Yvonne Moncada
Ing. Gustavo Moncada 8585 Piso 20-A
Buenos Aires
1010
Argentina
12
Cactus Comidas para llevar
Patricio Simpson
Cerrito 333
Buenos Aires
1010
Argentina
15
Piccolo und mehr
Georg Pipps
Geislweg 14
Salzburg
5020
Austria
16
Ernst Handel
Roland Mendel
Kirchgasse 6
Graz
8010
Austria
21
Maison Dewey
Catherine Dewey
Rue Joseph-Bens 532
Bruxelles
B-1180
Belgium
22
Suprêmes délices
Pascale Cartrain
Boulevard Tirou, 255
Charleroi
B-6000
Belgium
23
Queen Cozinha
Lúcia Carvalho
Alameda dos Canàrios, 891
São Paulo
05487-020
Brazil
34
Hanari Carnes
Mario Pontes
Rua do Paço, 67
Rio de Janeiro
05454-876
Brazil
31
Gourmet Lanchonetes
André Fonseca
Av. Brasil, 442
Campinas
04876-786
Brazil
41
Que Delícia
Bernardo Batista
Rua da Panificadora, 12
Rio de Janeiro
02389-673
Brazil
47
Wellington Importadora
Paula Parente
Rua do Mercado, 12
Resende
08737-363
Brazil
48
Ricardo Adocicados
Janete Limeira
Av. Copacabana, 267
Rio de Janeiro
02389-890
Brazil
49
Comércio Mineiro
Pedro Afonso
Av. dos Lusíadas, 23
São Paulo
05432-043
Brazil
51
Tradição Hipermercados
Anabela Domingues
Av. Inês de Castro, 414
São Paulo
05634-030
Brazil
51
Familia Arquibaldo
Aria Cruz
Rua Orós, 92
São Paulo
05442-030
Brazil
52
Mère Paillarde
Jean Fresnière
43 rue St. Laurent
Montréal
H1J 1C3
Canada
54
Laughing Bacchus Wine Cellars
Yoshi Tannamuri
1900 Oak St.
Vancouver
V3F 2K1
Canada
55
Bottom-Dollar Marketse
Elizabeth Lincoln
23 Tsawassen Blvd.
Tsawassen
T2F 8M4
Canada
83
Vaffeljernet
Palle Ibsen
Smagsløget 45
Århus
8200
Denmark
73
Simons bistro
Jytte Petersen
Vinbæltet 34
København
1734
Denmark
87
Wartian Herkku
Pirkko Koskitalo
Torikatu 38
Oulu
90110
Finland
90
Hose Ebe
Matti Karttunen
Keskuskatu 45
Helsinki
21240
Finland
18
resturant
Janine Labrune
67, rue des Cinquante Otages
Nantes
44000
France
7
Blondel père et fils
Frédérique Citeaux
24, place Kléber
Strasbourg
67000
France
26
France restauration
Carine Schmitt
54, rue Royale
Nantes
44000
France
INSERT INTO Syntax
you can write the INSERT INTO statement in two ways.
First, specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
SQL DELETE Example
This SQL statement deletes the customer "AOOO BOSS" from the "Customers" table:
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
1
AOOO BOSS
mr.AOOO BOSS
AOO Str. 57
Berlin
12209
Germany
2
SAAA PASS
Ana SAA PA
Awada Cony 2222
México
5021
Mexico
3
A Taper
More
eros 2312
Mali
95023
Mali
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WAfa
UK
5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22000
Sweden
Example
DELETE FROM Customers
WHERE CustomerName='AOOO BOSS';
The "Customers" table will now look like this:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
2
Ana Tof Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México .
05021
Mexico
3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK
5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden
Piccolo und mehr
Georg Pipps
Geislweg 14
Salzburg
5020
Austria
16
Ernst Handel
Roland Mendel
Kirchgasse 6
Graz
8010
Austria
21
Maison Dewey
Catherine Dewey
Rue Joseph-Bens 532
Bruxelles
B-1180
Belgium
22
Suprêmes délices
Pascale Cartrain
Boulevard Tirou, 255
Charleroi
B-6000
Belgium
23
Queen Cozinha
Lúcia Carvalho
Alameda dos Canàrios, 891
São Paulo
05487-020
Brazil
34
Hanari Carnes
Mario Pontes
Rua do Paço, 67
Rio de Janeiro
05454-876
Brazil
31
Gourmet Lanchonetes
André Fonseca
Av. Brasil, 442
Campinas
04876-786
Brazil
41
Que Delícia
Bernardo Batista
Rua da Panificadora, 12
Rio de Janeiro
02389-673
Brazil
47
Wellington Importadora
Paula Parente
Rua do Mercado, 12
Resende
08737-363
Brazil
48
Ricardo Adocicados
Janete Limeira
Av. Copacabana, 267
Rio de Janeiro
02389-890
Brazil
49
Comércio Mineiro
Pedro Afonso
Av. dos Lusíadas, 23
São Paulo
05432-043
Brazil
51
Tradição Hipermercados
Anabela Domingues
Av. Inês de Castro, 414
São Paulo
05634-030
Brazil
51
Familia Arquibaldo
Aria Cruz
Rua Orós, 92
São Paulo
05442-030
Brazil
52
Mère Paillarde
Jean Fresnière
43 rue St. Laurent
Montréal
H1J 1C3
Canada
54
Laughing Bacchus Wine Cellars
Yoshi Tannamuri
1900 Oak St.
Vancouver
V3F 2K1
Canada
55
Bottom-Dollar Marketse
Elizabeth Lincoln
23 Tsawassen Blvd.
Tsawassen
T2F 8M4
Canada
83
Vaffeljernet
Palle Ibsen
Smagsløget 45
Århus
8200
Denmark
73
Simons bistro
Jytte Petersen
Vinbæltet 34
København
1734
Denmark
87
Wartian Herkku
Pirkko Koskitalo
Torikatu 38
Oulu
90110
Finland
90
Hose Ebe
Matti Karttunen
Keskuskatu 45
Helsinki
21240
Finland
18
resturant
Janine Labrune
67, rue des Cinquante Otages
Nantes
44000
France
7
Blondel père et fils
Frédérique Citeaux
24, place Kléber
Strasbourg
67000
France
26
France restauration
Carine Schmitt
54, rue Royale
Nantes
44000
France
INSERT INTO Syntax
you can write the INSERT INTO statement in two ways.
First, specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
SQL DELETE Example
This SQL statement deletes the customer "AOOO BOSS" from the "Customers" table:
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
1
AOOO BOSS
mr.AOOO BOSS
AOO Str. 57
Berlin
12209
Germany
2
SAAA PASS
Ana SAA PA
Awada Cony 2222
México
5021
Mexico
3
A Taper
More
eros 2312
Mali
95023
Mali
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WAfa
UK
5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22000
Sweden
Example
DELETE FROM Customers
WHERE CustomerName='AOOO BOSS';
The "Customers" table will now look like this:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico
3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK
5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden